import pymysql
from openpyxl import load_workbook

# 读取导出的FSC文件，把里面的内容写入mysql
# 指定D盘上Excel文件的完整路径
file_path = 'D:/FscData/downloaded_filecc.xlsx'
# 加载Excel文件
wb = load_workbook(file_path)
# 获取活动工作表
data_sheet = wb.worksheets[0]

# 数据库连接配置
db_config = {
    'host': '191.254.8.220',
    'user': 'root',
    'password': 'Szmsa@520',
    'db': 'spiders',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}
# 连接数据库
connection = pymysql.connect(**db_config)

try:
    # 开始事务
    with connection.cursor() as cursor:
        # 定义存放所有数据的数组
        all_data = []
        # 读取数据（例如，读取第一行和第一列）
        for i in range(2, data_sheet.max_row + 1):
            row_list = []
            for row in data_sheet[i]:
                row_list.append(str(row.value))
            all_data.append(row_list)
        print(all_data)

        # 拼装需要插入字段对应的%号个数
        dataValue = ", ".join(['%s'] * len(all_data[0]))

        print(dataValue)
        sql = f"INSERT INTO xt_fsc_inspect (type,submitted,date,port,imo_number,name,callsign,mmsi,flag,shiptype,psco,datekeellaid,classsociety,grosstonnage,deficiencies,detention,detentiondeficiencies,ship_risk_profile,priority,window_inspection_range) VALUES ({dataValue})"
        connection.cursor().executemany(sql, all_data)
        connection.commit()
finally:
    connection.close()





